﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Windows;
using System.Windows.Forms;



namespace CP2011_Assignment
{
    class ProductionSystem : Form, IDataController
    {
        private SqlConnection connection1 = new SqlConnection(Properties.Settings.Default.AssignmentDBConnectionString1);
        
        public void addToDatabase(Object product)
        {
            Product prod = (Product)product;
            String name = prod.getName();
            String desc = prod.getDesc();
            int qty = prod.getQty();

            if (connection1 == null)
            {
                Console.WriteLine("No Database");
            }
            ///SQL QUERY
            ///
            try
            {
            }
            catch (SqlException ex)
            {
            }

            String query = "INSERT into Product (Product_Name, Product_Desc, Product_QtyPerDay) values (@name, @desc, @qty)";
            SqlCommand insertCommand = new SqlCommand(query, connection1);

            ///CONVERT VARIABLE INTO SQL PARAMETERS
            insertCommand.Parameters.AddWithValue("@name", name);
            insertCommand.Parameters.AddWithValue("@desc", desc);
            insertCommand.Parameters.AddWithValue("@qty", qty);

            ///execute query
            connection1.Open();
            insertCommand.ExecuteNonQuery(); ///EXECUTE SQL QUERY
            connection1.Close();

            Console.WriteLine("record inserted!");  
        }
        public void deleteRecord(Object ts)
        {
            TempStorage tempData = (TempStorage)ts;
            int prodId = tempData.getValue1();

            ///checking whether the ID is in the database or not 
            try
            {
            }
            catch (SqlException ex)
            {
            }
            String checkQuery = "SELECT Product_ID FROM Product WHERE Product_ID = '" + prodId+"'";
            SqlCommand checkCommand = new SqlCommand(checkQuery, connection1);
            connection1.Open();
            SqlDataReader reader = checkCommand.ExecuteReader();

            ///if id exist, delete record 
            if (reader.Read())
            {
                connection1.Close();
                connection1.Open();
                String query = "DELETE Product WHERE Product_ID = " + prodId;
                SqlCommand insertCommand = new SqlCommand(query, connection1);
                insertCommand.ExecuteNonQuery();
                connection1.Close();
                Console.WriteLine("success");
            }

            else
            {
                Console.WriteLine("no such ID found!");
                connection1.Close();
            }
        }
        public void editRecord(String query, int id)
        {
            /*TempStorage tempData = (TempStorage)ts;
            int invId = tempData.getValue1();
            int invNewQty = tempData.getValue2();*/

            ///checking whether the ID is in the database or not 
            try
            {
            }
            catch (SqlException ex)
            {
            }
            String checkQuery = "SELECT Product_ID FROM Product WHERE Product_ID = " + id;
            SqlCommand checkCommand = new SqlCommand(checkQuery, connection1);
            connection1.Open();
            SqlDataReader reader = checkCommand.ExecuteReader();

            ///if id exist, update value 
            if (reader.Read())
            {
                connection1.Close();
                connection1.Open();
                ///String query = "UPDATE Inventory SET Inv_QtyOnHand =" + invNewQty + "WHERE Inv_ID = " + invId;
                SqlCommand insertCommand = new SqlCommand(query, connection1);
                insertCommand.ExecuteNonQuery();
                connection1.Close();
            }

            else
            {
                MessageBox.Show("No such ID found!", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                connection1.Close();
            }
        }

        /// <summary>
        /// additional methods to add recipe to the recipe record
        /// </summary>
        /// <param name="ts"></param>
        /// <param name="prodName"></param>
        public void addRecipe(Object ts, String prodName)
        {
            //get the value needed from temporary storage
            TempStorage tempStorage = (TempStorage)ts;
            String invName = tempStorage.getString1();
            double invQtyNeeded = tempStorage.getValue3();
            double qtyPerPiece = tempStorage.getValue4();
       

            ///"convert" product name to product ID
           
                SqlCommand adHoc1 = connection1.CreateCommand();
                adHoc1.CommandText = "SELECT Product_ID FROM Product WHERE Product_Name = '" + prodName + "'";
                connection1.Open();
                int prodId = ((int)adHoc1.ExecuteScalar());
                connection1.Close();

                ///"convert" inv name to inv ID
                SqlCommand adHoc2 = connection1.CreateCommand();
                adHoc2.CommandText = "SELECT Inv_ID FROM Inventory WHERE Inv_Name = '" + invName + "'";
                
                try
                {
                    connection1.Open();
                    int invId = ((int)adHoc2.ExecuteScalar());
                    connection1.Close();

                    if (connection1 == null)
                    {
                        Console.WriteLine("No Database");
                    }
                    ///SQL QUERY
                    String query = "INSERT into Recipe (Inv_ID, Product_ID, Recipe_QtyPerDay, Recipe_QtyPerPiece) values (@invId, @prodId, @qtyNeeded, @qtyPerPiece)";
                    SqlCommand insertCommand = new SqlCommand(query, connection1);

                    ///CONVERT VARIABLE INTO SQL PARAMETERS
                    insertCommand.Parameters.AddWithValue("@invId", invId);
                    insertCommand.Parameters.AddWithValue("@prodId", prodId);
                    insertCommand.Parameters.AddWithValue("@qtyNeeded", invQtyNeeded);
                    insertCommand.Parameters.AddWithValue("@qtyPerPiece", qtyPerPiece);

                    ///execute query
                    connection1.Open();
                    insertCommand.ExecuteNonQuery(); ///EXECUTE SQL QUERY
                    connection1.Close();
                    Console.WriteLine("record inserted!");  
                }
                catch
                {
                    
                }
                


                
            
            

           
        }

        public void consumeItem()
        {
            bool consume = true;
            try
            {
                SqlCommand queryID = connection1.CreateCommand();
                queryID.CommandText = "SELECT DISTINCT Inventory.Inv_ID FROM Inventory INNER JOIN Recipe ON Inventory.Inv_ID =Recipe.Inv_ID";
                connection1.Open();
                SqlDataReader reader = queryID.ExecuteReader();


                ArrayList id = new ArrayList();
                while (reader.Read())
                {
                    id.Add(reader.GetInt32(0));
                }

                connection1.Close();

 
                for (int i = 0; i < id.Count; i++)
                {
                    int invID = (int)id[i];


                    //select the sum that will be decreased from inventory
                    SqlCommand query1 = connection1.CreateCommand();
                    query1.CommandText = "SELECT SUM(Recipe_QtyPerDay) FROM Recipe WHERE Inv_ID = " + invID;
                    connection1.Open();
                    int sum = ((int)query1.ExecuteScalar());
                    connection1.Close();

                    //select quantity on hand and decrease with sum (local variable)
                    SqlCommand query3 = connection1.CreateCommand();
                    query3.CommandText = "SELECT Inv_QtyOnHand FROM Inventory WHERE Inv_ID = " + invID;
                    connection1.Open();
                    int tempQty = (((int)query3.ExecuteScalar()) - sum);
                    connection1.Close();
                    if (tempQty < 0)
                    {
                        MessageBox.Show("Can't do the production, insufficient quantity on hand. Inventory with ID = " + invID, "Low Inventory!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        consume = false;
                    }
                    else if (consume == true)
                    {
                        SqlCommand query2 = connection1.CreateCommand();
                        query2.CommandText = "UPDATE Inventory SET Inv_QtyOnHand -=" + sum + "WHERE Inv_ID = " + invID;
                        connection1.Open();
                        query2.ExecuteNonQuery();
                        connection1.Close();
                    }

                }
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Fail! Please try again later", "Fail!", MessageBoxButtons.OK, MessageBoxIcon.Information);
           
            }
            
           if (consume == true)
           {
               MessageBox.Show("Successful! It's Fresh From the Oven!", "Successful!", MessageBoxButtons.OK, MessageBoxIcon.Information);
           }
        }

        public void checkRecord(String query)
        {
            
        }

        public ArrayList getRecord(String fillQuery)
        {
            ArrayList record = new ArrayList();
            try
            {
                SqlCommand getInv = connection1.CreateCommand();
                getInv.CommandText = fillQuery;
                connection1.Open();
                SqlDataReader reader = getInv.ExecuteReader();
                while (reader.Read())
                {
                    record.Add(reader.GetString(0));
                }
            }
            catch (SqlException ex)
            {
                return null;
            }
            
            return record;
        }
    }
}
